import datetime
import re  # 导入正则表达式模块，用于手机号格式验证

from flask import request, jsonify, current_app,session  # 从Flask框架中导入相关模块
from app import redis, db  # 导入redis数据库实例，SQLAlchemy数据库实例，和Flask管理模块
from app.api_v1_0 import api  # 导入API蓝图
from app.models import GeologyWP, SoilWP, GeologyPro, SoilPro, SoilStatistics, zoologyWP

from app.utils.response_code import RET  # 导入自定义的响应码

from sqlalchemy import func
from app import constants


@api.route('/get_dzworkpoints', methods=['POST'])
def get_dzWKP():

    try:
        data = db.session.query(GeologyWP).filter(GeologyWP.time=="2021/04")
    except Exception as e:
        db.session.rollback()  # 回滚数据库会话
        current_app.logger.error(e)  # 记录错误日志
        return jsonify(errno=RET.DBERR, errmsg="查询数据库异常")  # 返回数据库查询错误

    end = []
    for tmp in data:
        end.append({
            "id": tmp.id,
            "county": tmp.county,
            "town": tmp.town,
            "longitude": tmp.longitude,
            "latitude": tmp.latitude,
            "id_sample": tmp.id_sample,
            "time_batch": tmp.time_batch,
            "time": tmp.time
        })

    # 返回结果
    return jsonify(errno=RET.OK, errmsg="查询成功", data=end)

@api.route('/get_trworkpoints', methods=['POST'])
def get_trWKP():

    try:
        data = db.session.query(SoilWP).filter(SoilWP.time=="2021/04")
    except Exception as e:
        db.session.rollback()  # 回滚数据库会话
        current_app.logger.error(e)  # 记录错误日志
        return jsonify(errno=RET.DBERR, errmsg="查询数据库异常")  # 返回数据库查询错误

    end = []
    for tmp in data:
        end.append({
            "id": tmp.id,
            "county": tmp.county,
            "town": tmp.town,
            "longitude": tmp.longitude,
            "latitude": tmp.latitude,
            "id_sample": tmp.id_sample,
            "time_batch": tmp.time_batch,
            "time": tmp.time
        })

    # 返回结果
    return jsonify(errno=RET.OK, errmsg="查询成功", data=end)


@api.route('/search_WK', methods=['POST'])
def search_title():

    request_form = request.get_json().get('search_WK_req')
    print('----------------yes:', request_form)

    county = request_form.get('county')
    title = request_form.get('title')
    time = request_form.get('time')


    print(f"前端为：title:{title}, county:{county}, time:{time}")

    try:
        if title == 'dz':
            data = db.session.query(GeologyWP).filter(GeologyWP.county == county, GeologyWP.time.in_(time))
        elif title == 'tr':

            data = db.session.query(SoilWP).filter(SoilWP.county == county, SoilWP.time.in_(time))

        elif title == 'zoology':
            geologyNums = request_form.get('geologyNums')

            if (geologyNums == ''):
                data = db.session.query(zoologyWP).filter(zoologyWP.county == county, zoologyWP.time.in_(time))
                print('.......yes1')
            else:
                print('.......yes2')
                chars = request_form.get('chars')
                nums = int(request_form.get('nums'))
                print("*********************************", nums)
                if(chars=='bigger'):
                    print('--------------enter1')
                    data = db.session.query(zoologyWP).filter(zoologyWP.county == county, zoologyWP.time.in_(time), getattr(zoologyWP, geologyNums).__ge__(nums))
                else:
                    print('--------------enter2')
                    data = db.session.query(zoologyWP).filter(zoologyWP.county == county, zoologyWP.time.in_(time),
                                                           getattr(zoologyWP, geologyNums).__lt__(nums))
    except Exception as e:
        db.session.rollback()  # 回滚数据库会话
        current_app.logger.error(e)  # 记录错误日志
        return jsonify(errno=RET.DBERR, errmsg="查询数据库异常")  # 返回数据库查询错误

    end = []
    for tmp in data:
        item = {
            "id": tmp.id,
            "county": tmp.county,
            "town": tmp.town,
            "longitude": tmp.longitude,
            "latitude": tmp.latitude,
            "id_sample": tmp.id_sample,
            "time_batch": tmp.time_batch,
            "time": tmp.time,
            "title": title,
        }
        if hasattr(tmp, 'fcover'):
            if tmp.fcover is not None:
                item["fcover"] = tmp.fcover

        end.append(item)

    print('-------------', end)

    # 返回结果
    return jsonify(errno=RET.OK, errmsg="查询成功", data=end)

@api.route('/getdzwkPro_byidSample', methods=['POST'])
def getdzwkPro():

    request_form = request.get_json().get('id_sample')
    print('----------------', request_form)

    try:
        data = db.session.query(GeologyPro).filter(GeologyPro.id_sample.in_(request_form))
    except Exception as e:

        current_app.logger.error(e)  # 记录错误日志
        return jsonify(errno=RET.DBERR, errmsg="查询数据库异常")  # 返回数据库查询错误

    end = []
    for tmp in data:
        end.append({
            "id": tmp.id,
            "id_sample": tmp.id_sample,
            "Class": tmp.Class,
            "number": tmp.number,
            "slide_number":tmp.slide_number,
            "slide_volume":tmp.slide_volume,
            "slide_description":tmp.slide_description,
            "mudslide_number":tmp.mudslide_number,
            "mudslide_volume":tmp.mudslide_volume,
            "collapse_number":tmp.collapse_number,
            "collapse_volume":tmp.collapse_volume,
            "collapse_description":tmp.collapse_description
        })

    print('-------------', end)

    # 返回结果
    return jsonify(errno=RET.OK, errmsg="查询成功", data=end)

@api.route('/getsoilwkPro', methods=['POST'])
def getsoilwkPro():


    try:
        data = db.session.query(SoilPro).all()
    except Exception as e:

        current_app.logger.error(e)  # 记录错误日志
        return jsonify(errno=RET.DBERR, errmsg="查询数据库异常")  # 返回数据库查询错误

    end = []
    for tmp in data:
        end.append({
            "id": tmp.id,
            "county": tmp.county,
            "bulk": tmp.bulk,
            "humidity": tmp.humidity,
            "ph": tmp.ph,
            "som":tmp.som,
            "tn":tmp.tn,
            "tp":tmp.tp,
            "tk":tmp.tk,
            "tc":tmp.tc,
            "available_p":tmp.available_p,
        })

    print('-------------', end)

    # 返回结果
    return jsonify(errno=RET.OK, errmsg="查询成功", data=end)

@api.route('/getsoil_spatialtable', methods=['POST'])
def getsoiltable():

    request_form = request.get_json().get('search_req')


    county = request_form.get('county')
    print('----------------', request_form, county)

    try:
        if(county == 'ALL'):
            data = db.session.query(SoilStatistics).all()
        else:
            data = db.session.query(SoilStatistics).filter(SoilStatistics.county == county).all()
    except Exception as e:

        current_app.logger.error(e)  # 记录错误日志
        return jsonify(errno=RET.DBERR, errmsg="查询数据库异常")  # 返回数据库查询错误

    end = []
    for tmp in data:
        end.append({
            "id": tmp.id,
            "county":tmp.county,
            "c": tmp.c,
            "d": tmp.d,
            "m": tmp.m,
            "n": tmp.n,
            "q":tmp.q,
            "u":tmp.u,
            "f":tmp.f,
            "o":tmp.o,
            "e":tmp.e,
            "k":tmp.k,
        })

    print('-------------', end)

    # 返回结果
    return jsonify(errno=RET.OK, errmsg="查询成功", data=end)

@api.route('/getAllDisater', methods=['POST'])
def getAllDisater():


    try:
        data = db.session.query(GeologyWP.county, func.sum(GeologyPro.number).label('total_number')).join(
            GeologyPro,GeologyPro.id_sample == GeologyWP.id_sample
        ).group_by(
            GeologyWP.county
        ).all()

    except Exception as e:

        current_app.logger.error(e)  # 记录错误日志
        return jsonify(errno=RET.DBERR, errmsg="查询数据库异常")  # 返回数据库查询错误

    end = []
    for tmp in data:
        end.append({
            "county":tmp[0],
            "number":tmp[1]
        })

    print('-------------', end)

    # 返回结果
    return jsonify(errno=RET.OK, errmsg="查询成功", data=end)


@api.route('/get_zoologyworkpoints', methods=['POST'])
def get_zoologyWKP():

    try:
        data = db.session.query(zoologyWP).filter(SoilWP.time=="2021/04")
    except Exception as e:
        db.session.rollback()  # 回滚数据库会话
        current_app.logger.error(e)  # 记录错误日志
        return jsonify(errno=RET.DBERR, errmsg="查询数据库异常")  # 返回数据库查询错误

    end = []
    for tmp in data:
        end.append({
            "id": tmp.id,
            "county": tmp.county,
            "town": tmp.town,
            "longitude": tmp.longitude,
            "latitude": tmp.latitude,
            "id_sample": tmp.id_sample,
            "time_batch": tmp.time_batch,
            "time": tmp.time,
            "fcover": tmp.fcover
        })

    # 返回结果
    return jsonify(errno=RET.OK, errmsg="查询成功", data=end)

@api.route('/getzoologywkPro', methods=['POST'])
def getzoologywkPro():


    try:
        data = db.session.query(zoologyWP).all()
    except Exception as e:

        current_app.logger.error(e)  # 记录错误日志
        return jsonify(errno=RET.DBERR, errmsg="查询数据库异常")  # 返回数据库查询错误

    end = []
    for tmp in data:
        end.append({
            "id": tmp.id,
            "county": tmp.county,
            "id_sample": tmp.id_sample,
            "number_plot": tmp.number_plot,
            "number_altitude_zone": tmp.number_altitude_zone,
            "id_repair":tmp.id_repair,
            "name_community":tmp.name_community,
            "number_species":tmp.number_species,
            "dominate_species":tmp.dominate_species,
            "fcover":tmp.fcover,
            "fresh_under_biomass":tmp.fresh_under_biomass,
            "above_biomass":tmp.above_biomass,
            "name_repair":tmp.name_repair
        })

    print('-------------', end)

    # 返回结果
    return jsonify(errno=RET.OK, errmsg="查询成功", data=end)

@api.route('/getsoil_byType', methods=['POST'])
def getsoilPie():

    request_form = request.get_json().get('search_req')
    # print('----------------yes:', request_form)

    type = request_form.get('type')
    # print('11111111111111', type)


    try:
        data = db.session.query(SoilPro.county, func.sum(getattr(SoilPro, type)).label('total_bulk')).group_by(SoilPro.county).all()
        # print("@@@@@@@@@@@@@@@@@", data)
    except Exception as e:

        current_app.logger.error(e)  # 记录错误日志
        return jsonify(errno=RET.DBERR, errmsg="查询数据库异常")  # 返回数据库查询错误

    end = []
    for tmp in data:
        end.append({
            "name": tmp.county,
            "value": round(tmp.total_bulk,3)
        })

    # 返回结果
    return jsonify(errno=RET.OK, errmsg="查询成功", data=end)
